package book;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import common.DBManager;

public class RentalServlet extends HttpServlet {

	Statement smt 		= null;
	Connection con 		= null;
	ResultSet rs 		= null;

	String PATH_P202 =  "/book/SearchServlet";
	int rental_cnt ;
	int reserve_cnt ;
	int rentalOk ;
	String message;



	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			                          throws ServletException, IOException {
		doPost(req, resp);
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			                            throws ServletException, IOException {

        //　入力パラメータの文字エンコーディング
		req.setCharacterEncoding("UTF-8");
		resp.setContentType("text/html;charset=UTF-8");

		String member_id = (String)req.getParameter("member_id");//ﾕｰｻﾞ管理ID
		String id = (String)req.getParameter("book_id");         //書籍ID

		//データベース呼び出し
		con = DBManager.getConnection();


//=========MmberIDと、貸出可・不可のチェック==========
		boolean isMember 	= isMember(member_id);
		boolean canRental 	= canRental(member_id);

		if (isMember) {
			if (canRental) {
				rental(req, resp);
			}
			else {
				req.setAttribute("msg", "貸出と予約を含め１０冊を超えるため貸出はできません");
				// P202 書籍検索結果画面へ遷移
				RequestDispatcher dispatcher = req.getRequestDispatcher(PATH_P202);
				dispatcher.forward(req, resp);
			}
		}
		else {
			req.setAttribute("msg", "ユーザー管理IDがありません");
			req.setAttribute("error2", "※");
			// P241 書籍貸出画面へ戻る
			RequestDispatcher dispatcher = req.getRequestDispatcher("/book/RentalEntryServlet?id=" + id);
			dispatcher.forward(req, resp);
		}
	}


	private boolean isMember(String member_id)
	{
		boolean ret = false;
		try{
			String sql_members = "select * from members where id = ?";
			PreparedStatement ps = con.prepareStatement(sql_members);
			ps.setString(1, member_id);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				ret = true;
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	private boolean canRental(String member_id)
	{
		boolean ret = false;

		//	ﾕｰｻﾞ管理IDで貸出テーブルに登録済みの件数を取得する
		try{
			String sql_rentals = "select count(*) as cnt from rentals where member_id = ?";
			PreparedStatement ps = con.prepareStatement(sql_rentals);
			ps.setString(1, member_id);

			ResultSet rs = ps.executeQuery();

				if (rs.next()) {
					rental_cnt = rs.getInt("cnt");
				}
		}catch (SQLException e) {
			e.printStackTrace();
		}

		//	ﾕｰｻﾞ管理IDで予約テーブルに登録済みの件数を取得する
		try{
			String sql_rsv = "select count(*) as cnt from reserves where member_id = ?";
			PreparedStatement ps_rsv = con.prepareStatement(sql_rsv);
			ps_rsv.setString(1, member_id);

			ResultSet rs_rsv = ps_rsv.executeQuery();

				if(rs_rsv.next()){
					reserve_cnt = rs_rsv.getInt("cnt");
				}
		}catch (SQLException e) {
			e.printStackTrace();
		}

		//貸出可能かどうかの判断
		//	今回分（１冊）+予約済み＋貸出済み
		//			１１冊以内ならＯＫ
		//			１２冊以上なら「貸出と予約を含め１０冊を超えるため貸出はできません。」を書籍検索結果に表示する
		rentalOk = 1 + rental_cnt + reserve_cnt;
		if(rentalOk < 12){
			 ret = true;
//			message = "貸出と予約を含め１０冊を超えるため貸出はできません。";
//			req.setAttribute("msg", message);
			// P202 書籍検索結果画面へ遷移
//			RequestDispatcher dispatcher = req.getRequestDispatcher(PATH_P202);
//			dispatcher.include(req, resp);
		}

		return ret;
	}

//=======呼び出されるクラス=======

	private void rental (HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {

        //　入力パラメータの取得
		String id = (String)req.getParameter("book_id");                       //書籍ID
		String member_id = (String)req.getParameter("member_id");              //ﾕｰｻﾞ管理ID
		String rental_date = (String)req.getParameter("rental_date");          //貸出日
		String return_plan_date = (String)req.getParameter("return_plan_date");//返却予定日
		String reserve_id = (String)req.getParameter("reserve_id");			   //予約ID
		String book_isbn = (String)req.getParameter("book_isbn");		       //ISBN


		String path = "/book/SearchServlet";


		try {
			con.setAutoCommit(false);
			smt  = con.createStatement();

			String  isql = "insert into rentals(BOOK_ID,MEMBER_ID,RENTAL_DATE,RETURN_PLAN_DATE,RESERVE_ID) value(?,?,?,?,?)";

			PreparedStatement ist = con.prepareStatement(isql);

			ist.setInt(1, Integer.parseInt(id));
			ist.setInt(2, Integer.parseInt(member_id));
			ist.setString(3, rental_date);
			ist.setString(4, return_plan_date);

			System.out.println(reserve_id);

			if(reserve_id == null || reserve_id.length() == 0){
				ist.setNull(5, java.sql.Types.INTEGER);
			}else{
				ist.setInt(5, Integer.parseInt(reserve_id));
			}

			ist.executeUpdate();

			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery("select last_insert_id() as new_id");

			//貸出IDの取得
			int new_id = 0;
			if(rs.next()){
				new_id = rs.getInt("new_id");
			}


			//　書籍テーブルの貸出IDを更新

			String sql_rental_id = "Update books set rental_id = ? where id = ?";

			PreparedStatement ist_newest = con.prepareStatement(sql_rental_id);

			ist_newest.setInt(1,new_id);//登録した貸出IDをセット
			ist_newest.setInt(2,Integer.parseInt(id));

			ist_newest.executeUpdate();


			//予約テーブルをISBNで検索して、存在したら一番古い予約レコードを削除する

			String sql_reserves = "delete from reserves where book_isbn=? order by id limit 1";

			PreparedStatement reseves_sql = con.prepareStatement(sql_reserves);

			reseves_sql.setString(1,book_isbn);//登録した貸出IDをセット

			reseves_sql.executeUpdate();

			con.commit();

		}catch (SQLException e){
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new ServletException(e);
		}finally{
			if(smt != null){
				try{smt.close();}
				catch(Exception e){}
			if(con != null){
				try{con.close();}
				catch(Exception e){}
			}
			}

		}

		String message;
		message = "貸出を完了しました";
		req.setAttribute("msg", message);

		if(reserve_id ==null || "".equals(reserve_id)){
			// P202 書籍検索結果画面へ遷移
			RequestDispatcher dispatcher = req.getRequestDispatcher(path);
			dispatcher.include(req, resp);

		}else{
			// P151 予約者一覧画面へ遷移
			RequestDispatcher dispa = req.getRequestDispatcher("/member/ReserveListServlet");
			dispa.include(req, resp);
		}
	}
}
